/**
 * @Title: GroupSum.java
 * @Package tbg
 * @Description: TODO
 * @author tbc
 * @date 2016年6月9日 上午1:16:48
 * @version
 */
package tbg;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;
import java.util.concurrent.TimeUnit;

/**
 * 最后一列是数量，前面的列分组，对最后一列求和<br>
 * 等价于SQL ： select a,b,c,sum(x) from table group by a,b,c <br>
 * 取值，如果是string按string类型get，其它用getRawValue();
 * C:\\Users\\tbc\\Desktop\\长安CS35-2016年1-10月份.xlsx
 *
 * @author tbc
 * @version 1.0 create:{2016年6月9日 上午1:16:48}
 */
public class GroupSum {

    private static XSSFWorkbook book = null;
    private static XSSFSheet sheet = null;

    private static double toDouble(String source) {
        return toDouble(source, 0.0);
    }

    private static double toDouble(String source, Double def) {
        try {
            return Double.parseDouble(source);
        } catch (Exception e) {
            return def;
        }
    }

    private static long toLong(String source) {
        return toLong(source, 0L);
    }

    private static long toLong(String source, Long def) {
        try {
            return Long.parseLong(source);
        } catch (Exception e) {
            return def;
        }
    }

    private static Object toCell(String source) {
        try {
            return Double.parseDouble(source);
        } catch (Exception e) {
            return source;
        }
    }

    private static boolean checkDouble(String s) {
//                StringUtil.isNumeric("");
//        Character.isDigit("".toCharArray()[0])
        if (Objects.isNull(s))
            return false;
        if (s.contains("E") || s.contains("e"))
            return false;
        try {
            Double.parseDouble(s);
            return true;
        } catch (Exception e) {
            return false;
        }

    }

    private static boolean checkLong(String s) {
        if (Objects.isNull(s))
            return false;
        //排除科学计数法
        if (s.contains("E") || s.contains("e"))
            return false;
        try {
            Long.parseLong(s);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    public static List<List<String>> readExcelData() {
        Row row0 = sheet.getRow(0);
        // 总列数
        // int length = row0.getLastCellNum();
        // 总行数
        int rowNums = sheet.getLastRowNum();

        List<List<String>> datas = new ArrayList<>();
        // 处理头行
        List<String> row0List = new ArrayList<>();
        row0.forEach(it -> {
            row0List.add(it.getStringCellValue());
        });
        datas.add(row0List);
        // 去掉第0行，从第一行开始读取
        // sheet.getLastRowNum() = _rows.lastKey();跟Cell不一样，切记切记！！！
        for (int i = 1; i <= rowNums; i++) {
            // List<String> rowList = new ArrayList<>();
            XSSFRow row = sheet.getRow(i);
            List<String> cellList = new ArrayList<>();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                XSSFCell cell = row.getCell(j);
                if (null == cell) {
                    cellList.add(null);
                    continue;
                }
                String value = "";
                if (1 == cell.getCellType()) {
                    value = cell.getStringCellValue();
                } else {
                    value = cell.getRawValue();
                }
                cellList.add(value);
            }
            datas.add(cellList);
        }

        return datas;
    }

    public static List<List<String>> writeList(List<List<String>> list) {
        String separator = "@#@#";
        List<String> first = list.get(0);
        list.remove(0);
        final Map<String, Integer> map = new HashMap<>();
        list.forEach(it -> {
            StringBuffer sb = new StringBuffer();
            for (int i = 1; i < it.size(); i++) {
                sb.append(it.get(i - 1)).append(separator);
            }

            String k = sb.toString();
            Integer v = Integer.valueOf(it.get(it.size() - 1));
            if (!map.containsKey(k)) {
                map.put(k, v);
            } else {
                map.put(k, v + map.get(k));
            }
        });
        List<List<String>> endList = new ArrayList<>();
        endList.add(first);
        map.forEach((k, v) -> {
            List<String> tempList = new ArrayList<>();
            String[] ss = k.split(separator);
            for (String s : ss) {
                tempList.add(s);
            }
            tempList.add(v.toString());
            endList.add(tempList);
        });

        return endList;
    }

    public static void writeExcel(String path, List<List<String>> list) {
        // XSSFWorkbook outBook = new XSSFWorkbook();
        System.out.println("--> 创建sheet页“整理” ");
        XSSFSheet sheet = null;
        try {
            sheet = book.createSheet("整理");
        } catch (java.lang.IllegalArgumentException e) {
            System.out.println("创建sheet页失败，信息：" + e.getMessage());
            try {
                TimeUnit.SECONDS.sleep(3);
            } catch (InterruptedException e1) {
                e1.printStackTrace();
            }
            System.exit(0);
        }
        // 清除该sheet页的内容
        // for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        // sheet.removeRowBreak(i);
        // }
        System.out.println("--> 创建sheet页“整理”成功");

        System.out.println("--> 开始设置sheet页“整理”的内容...");
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.createRow(i);
            List<String> rowData = list.get(i);
            String k12 = "            -->";
            System.out.println("---> 第 " + i + "行..." + k12 + rowData);

            for (int j = 0; j < rowData.size(); j++) {
                Cell cell = row.createCell(j);
                // h:\\Users\\tbc\\Desktop\\江淮5.xlsx
                // 最后一列且不是第一行，是数量，设置格式
//                if (j == (rowData.size() - 1) && i != 0) {
//                    cell.setCellType(0);
//                    cell.setCellType(0);
//                    cell.setCellValue(toDouble(rowData.get(j)));
//                    continue;
//                }
//                cell.setCellValue(rowData.get(j));
                String value = rowData.get(j);
                if (checkLong(value)) {
                    cell.setCellValue(toLong(value));
                } else if (checkDouble(value)) {
                    cell.setCellValue(toDouble(value));
                } else {
                    cell.setCellValue(value);
                }
            }
        }
        System.out.println("--> 数据设置完成");
        System.out.println("正在输出到文件 ...");
        // 输出到另一个文件
        // String extension = path.substring(path.lastIndexOf(".xls"));
        // String outFile = path.substring(0, path.length() - 4) + "_pro" +
        // extension;
        File f = new File(path);
        OutputStream outputstream = null;
        try {
            outputstream = new FileOutputStream(f);
            book.write(outputstream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                assert outputstream != null;
                outputstream.flush();
                outputstream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        System.out.println("完成");
        try {
            TimeUnit.SECONDS.sleep(1);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) throws InterruptedException {
        @SuppressWarnings("resource")
        Scanner s = new Scanner(System.in);
        String from = s.nextLine();
        if (from.startsWith("\"")) {
            System.out.println("文件路径带引号，尝试去除...");
            from = from.substring(1, from.length() - 1);
        }
        System.out.println("开始读取文件：" + from + " ...");

        InputStream in = null;
        try {
            in = new FileInputStream(from);

            book = new XSSFWorkbook(in);

        } catch (FileNotFoundException e) {
            System.out.println("读取文件失败，文件不存在或者文件名有特殊符号。。。最好是只有英文字母");
            System.out.println("程序自动退出，修改文件路径或者文件名再试试");
            TimeUnit.SECONDS.sleep(2);
            e.printStackTrace();
        } catch (IOException e) {
            System.out.println("加载excel失败，不知道为什么 ，程序退出。。。");
            System.out.println(e.getMessage());
            TimeUnit.SECONDS.sleep(2);
        }

        sheet = book.getSheet("1");
        if (sheet == null) {
            System.out.println("no sheet ‘1’ ... ");
            System.exit(0);
        }

        System.out.println("---> 获取sheet页\"1\"成功... ");
        System.out.println("---> 开始读取sheet页\"1\"的数据...");
        // 读取数据
        List<List<String>> datas = readExcelData();

        System.out.println("---> 数据读取成功");
        System.out.println("---> 正在进行合并分组求和处理 ...");
        // 处理数据格式
        List<List<String>> endDatas = writeList(datas);
        // endDatas.forEach(it -> {
        // System.out.print(it);
        // System.out.println();
        // });

        System.out.println("合并完成，正在写入文件...");
        // 写回到excel文件
        writeExcel(from, endDatas);

        // CloseUtil.close(in);

    }

}
